﻿Imports System.Data.OleDb

Public Class DeleteRating
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim prevPage As String = String.Empty
        If Request.Params("ID") = "" Then Response.Redirect("Profile.aspx?User=" & User.Identity.Name)

        If (Not IsPostBack) Then
            prevPage = Request.UrlReferrer.ToString()
        End If

        'Parse the URL to get the value in the Querystring
        Dim getQueryString As String = Request.QueryString("ID")

        'Get the recipe ID from the COMMENT table before deleting the comment
        Dim RecDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
        Dim RecSqlString As String = "SELECT * FROM RATING WHERE RATING_ID=@f1"
        Dim RecCmd As OleDbCommand = New OleDbCommand(RecSqlString, RecDbConn)
        RecCmd.CommandType = CommandType.Text
        RecCmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
        RecDbConn.Open()
        Dim RecDataReader As OleDbDataReader = RecCmd.ExecuteReader
        Dim recipeID As String = ""
        If RecDataReader.HasRows Then
            RecDataReader.Read()
            If Not IsDBNull(RecDataReader("RECIPE_ID")) Then recipeID = RecDataReader("RECIPE_ID")
        End If
        RecCmd.Dispose()
        RecDbConn.Close()
        RecDbConn.Dispose()

        'Delete the comment from the COMMENT table
        Dim delRatingDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
        Dim delRatingSqlString = "DELETE from RATING where RATING_ID=@f1"
        Dim delRatingCommand As OleDbCommand = New OleDbCommand(delRatingSqlString, delRatingDbConn)
        delRatingCommand.CommandType = CommandType.Text
        delRatingCommand.Parameters.AddWithValue("@f1", getQueryString)
        delRatingDbConn.Open()
        delRatingCommand.ExecuteNonQuery()
        delRatingCommand.Dispose()
        delRatingDbConn.Close()
        delRatingDbConn.Dispose()

        'Read the new average and total ratings using the STAT_TOTAL_AVG_RATING view
        Dim ROleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
        Dim RSqlString As String = "SELECT * FROM STAT_TOTAL_AVG_RATING WHERE RECIPE_ID=@f1"
        Dim RCmd As OleDbCommand = New OleDbCommand(RSqlString, ROleDbConn)
        RCmd.CommandType = CommandType.Text
        RCmd.Parameters.AddWithValue("@f1", recipeID)
        ROleDbConn.Open()
        Dim RDataReader As OleDbDataReader = RCmd.ExecuteReader
        Dim newRating As String = ""
        Dim newTotalRating As String = ""
        If RDataReader.HasRows Then
            RDataReader.Read()
            If Not IsDBNull(RDataReader("TotalRatings")) Then newTotalRating = RDataReader("TotalRatings")
            If Not IsDBNull(RDataReader("AverageRating")) Then newRating = RDataReader("AverageRating")
        End If
        RCmd.Dispose()
        ROleDbConn.Close()
        ROleDbConn.Dispose()

        'Update the current rating and total number of ratings into the RECIPE table
        Dim RecipeOleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("recipeConnectionString").ConnectionString)
        Dim RecipeSqlString As String = "UPDATE RECIPE SET REC_STARS=@f1,REC_STARSTIMES=@f2 WHERE REC_ID=@f3"
        Dim RecipeCmd As OleDbCommand = New OleDbCommand(RecipeSqlString, RecipeOleDbConn)
        RecipeCmd.CommandType = CommandType.Text
        RecipeCmd.Parameters.AddWithValue("@f1", Val(newRating))
        RecipeCmd.Parameters.AddWithValue("@f2", Val(newTotalRating))
        RecipeCmd.Parameters.AddWithValue("@f3", recipeID)
        RecipeOleDbConn.Open()
        RecipeCmd.ExecuteNonQuery()
        RecipeOleDbConn.Close()
        RecipeOleDbConn.Dispose()

        Response.Redirect(prevPage)

    End Sub

End Class